package dao;

import bean.PageBean;
import bean.User;
import util.JdbcUtils;
import util.StringUtil;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.*;

/**
 * 骑行用户的Dao
 *
 * @author gloria
 */
public class UserDao {

    /**
     * Gets user name by user id.
     *
     * @param userId the user id
     * @return user name by user id
     * @throws Exception the exception
     * @author rookie
     */
    public String getUserNameByUserId(String userId) throws Exception {
        String sql = "select username from user where userId=?";
        List<Object> para = new ArrayList<>();
        para.add(userId);
        List<Object> list = JdbcUtils.queryForArrObject(sql, para, User.class);
        if (list.isEmpty()) {
            return null;
        } else {
            User user = (User) list.get(0);
            return user.getUserName();
        }
    }

    /**
     * 通过用户手机号获取用户id
     *
     * @param phone the phone
     * @return user id by phone
     * @throws Exception the exception
     * @author 邓晴予
     */
    public String getUserIdByPhone(String phone) throws Exception {
        String sql = "select userId from user where phone=?";
        List<Object> para = new ArrayList<>();
        para.add(phone);
        List<Object> list = JdbcUtils.queryForArrObject(sql, para, User.class);
        if (list.isEmpty()) {
            return null;
        } else {
            User user = (User) list.get(0);
            return user.getUserId();
        }
    }

    /**
     * 骑行用户登录
     *
     * @param user the user
     * @return user user
     * @throws Exception the exception
     */
    public User login(User user) throws Exception {
        String sql = "select * from user where phone=? and password=? and status!=-2";
        List<Object> para = new ArrayList<>();
        para.add(user.getPhone());
        para.add(user.getPassword());
        List<Object> list = JdbcUtils.queryForArrObject(sql, para, User.class);
        return list.isEmpty() ? null : (User) list.get(0);
    }

    /**
     * 骑行用户一览
     *
     * @param pageBean the page bean
     * @param user     the user
     * @return list list
     */
    public List<Object> userList(PageBean pageBean, User user) {
        StringBuilder sb = new StringBuilder("select * from user u ");
        List<Object> para = new ArrayList<>();
        if (StringUtil.isNotEmpty(user.getUserId())) {
            sb.append(" and u.userId =?");
            para.add(user.getUserId());
        }
        if (StringUtil.isNotEmpty(user.getUserName())) {
            sb.append(" and u.username = ?");
            para.add(user.getUserName());
        }
        if (StringUtil.isNotEmpty(user.getPassword())) {
            sb.append(" and u.password =?");
            para.add(user.getPassword());
        }
        if (StringUtil.isNotEmpty(user.getName())) {
            sb.append(" and u.name =?");
            para.add(user.getName());
        }
        if (StringUtil.isNotEmpty(user.getCardNumber())) {
            sb.append(" and u.cardNumber =?");
            para.add(user.getCardNumber());
        }
        if (StringUtil.isNotEmpty(user.getScore())) {
            sb.append(" and u.score =?");
            para.add(user.getScore());
        }
        if (StringUtil.isNotEmpty(user.getPhone())) {
            sb.append(" and u.phone =?");
            para.add(user.getPhone());
        }
        if (StringUtil.isNotEmpty(user.getStatus())) {
            sb.append(" and u.status =?");
            para.add(user.getStatus());
        } else {
            sb.append(" and u.status != -2 ");
        }
        if (StringUtil.isNotEmpty(user.getBalance())) {
            sb.append(" and u.balance =?");
            para.add(user.getBalance());
        }
        if (StringUtil.isNotEmpty(user.getUserInfoId())) {
            sb.append(" and u.userInfoId =?");
            para.add(user.getUserInfoId());
        }
        if (pageBean != null) {
            sb.append(" limit ").append(pageBean.getStart()).append(",").append(pageBean.getRows());
        }
        String sql = sb.toString().replaceFirst("and", "where");
        List<Object> list = JdbcUtils.queryForArrObject(sql, para, User.class);
        return list.isEmpty() ? Collections.emptyList() : list;
    }

    /**
     * 骑行用户删除
     *
     * @param delIds the del ids
     * @throws Exception the exception
     */
    public void userDelete(String delIds) throws Exception {
        String sql = "update user set status=-2 where userId in(?)";
        List<Object> para = new ArrayList<>();
        para.add(delIds);
        JdbcUtils.update(sql, para);
    }

    /**
     * 骑行用户添加
     *
     * @param user the user
     * @return boolean boolean
     * @throws Exception the exception
     */
    public boolean userAdd(User user) throws Exception {
        StringBuilder sb = new StringBuilder("insert into user values(");
        List<Object> para = new ArrayList<>();
        if (StringUtil.isNotEmpty(user.getUserId())) {
            sb.append(" ,?");
            para.add(user.getUserId());
        } else if (user.getUserId() == null) {
            sb.append(" ,null");
        }
        if (null != user.getUserName() && StringUtil.isNotEmpty(user.getUserName())) {
            sb.append(" ,?");
            para.add(user.getUserName());
        } else {
            sb.append(" ,?");
            para.add("用户" + (user.getPhone()));
        }
        if (null != user.getPassword() && StringUtil.isNotEmpty(user.getPassword())) {
            sb.append(" ,?");
            para.add(user.getPassword());
        } else {
            sb.append(" ,'888888'");
        }
        if (null != user.getName() && StringUtil.isNotEmpty(user.getName())) {
            sb.append(" ,?");
            para.add(user.getName());
        } else {
            sb.append(" ,?");
            para.add("用户" + (user.getPhone()));
        }
        if (null != user.getCardNumber() && StringUtil.isNotEmpty(user.getCardNumber())) {
            sb.append(" ,?");
            para.add(user.getCardNumber());
        } else {
            sb.append(" ,null");
        }
        if (null != user.getScore() && StringUtil.isNotEmpty(user.getScore())) {
            sb.append(" ,?");
            para.add(user.getScore());
        } else {
            sb.append(" ,null");
        }
        if (null != user.getPhone() && StringUtil.isNotEmpty(user.getPhone())) {
            sb.append(" ,?");
            para.add(user.getPhone());
        } else {
            return false;
        }
        if (null != user.getStatus() && StringUtil.isNotEmpty(user.getStatus())) {
            sb.append(" ,?");
            para.add(user.getStatus());
        } else {
            sb.append(" ,null");
        }
        if (null != user.getBalance() && StringUtil.isNotEmpty(user.getBalance())) {
            sb.append(" ,?");
            para.add(user.getBalance());
        } else {
            sb.append(" ,null");
        }
        if (null != user.getUserInfoId() && StringUtil.isNotEmpty(user.getUserInfoId())) {
            sb.append(" ,?");
            para.add(user.getUserInfoId());
        } else {
            sb.append(" ,null");
        }

        sb.append(")");
        String sql = sb.toString().replaceFirst(",", " ");
        return 1 == JdbcUtils.update(sql, para);
    }

    /**
     * 骑行用户修改
     *
     * @param user the user
     * @return int int
     * @throws Exception the exception
     */
    public int userModify(User user) throws Exception {
        StringBuilder sb = new StringBuilder("update user u ");
        List<Object> para = new ArrayList<>();
        if (StringUtil.isNotEmpty(user.getUserName())) {
            sb.append(" , u.username=?");
            para.add(user.getUserName());
        }
        if (StringUtil.isNotEmpty(user.getPassword())) {
            sb.append(" , u.password =?");
            para.add(user.getPassword());
        }
        if (StringUtil.isNotEmpty(user.getName())) {
            sb.append(" , u.name =?");
            para.add(user.getName());
        }
        if (StringUtil.isNotEmpty(user.getCardNumber())) {
            sb.append(" , u.cardNumber =?");
            para.add(user.getCardNumber());
        }
        if (StringUtil.isNotEmpty(user.getScore())) {
            sb.append(" , u.score =?");
            para.add(user.getScore());
        }
        if (StringUtil.isNotEmpty(user.getPhone())) {
            sb.append(" , u.phone =?");
            para.add(user.getPhone());
        }
        if (StringUtil.isNotEmpty(user.getStatus())) {
            sb.append(" , u.status=?");
            para.add(user.getStatus());
        }
        if (StringUtil.isNotEmpty(user.getBalance())) {
            sb.append(" , u.balance=?");
            para.add(user.getBalance());
        }
        sb.append(" where u.userId=?");
        para.add(user.getUserId());
        String sql = sb.toString().replaceFirst(",", "set");
        return JdbcUtils.update(sql, para);
    }

    /**
     * User count int.
     *
     * @param user the user
     * @return the int
     * @throws Exception the exception
     */
    public int userCount(User user) throws Exception {
        StringBuilder sb = new StringBuilder("select count(*) as total from user u");

        List<Object> para = new ArrayList<>();
        if (StringUtil.isNotEmpty(user.getUserId())) {
            sb.append(" and u.userId =? ");
            para.add(user.getUserId());
        }
        if (StringUtil.isNotEmpty(user.getUserName())) {
            sb.append(" and u.username = ?");
            para.add(user.getUserName());
        }
        if (StringUtil.isNotEmpty(user.getPassword())) {
            sb.append(" and u.password =?");
            para.add(user.getPassword());
        }
        if (StringUtil.isNotEmpty(user.getName())) {
            sb.append(" and u.name = ?");
            para.add(user.getName());
        }
        if (StringUtil.isNotEmpty(user.getCardNumber())) {
            sb.append(" and u.cardNumber =?");
            para.add(user.getCardNumber());
        }
        if (StringUtil.isNotEmpty(user.getScore())) {
            sb.append(" and u.score =?");
            para.add(user.getScore());
        }
        if (StringUtil.isNotEmpty(user.getPhone())) {
            sb.append(" and u.phone =?");
            para.add(user.getPhone());
        }
        if (StringUtil.isNotEmpty(user.getStatus())) {
            sb.append(" and u.status=?");
            para.add(user.getStatus());
        } else {
            sb.append(" and u.status!=-2 ");
        }
        if (StringUtil.isNotEmpty(user.getBalance())) {
            sb.append(" and u.balance=?");
            para.add(user.getBalance());
        }
        String sql = sb.toString().replaceFirst("and", "where");
        List<Map<String, String>> result = JdbcUtils.queryForMap(sql, para);
        return result.isEmpty() ? 0 : Integer.parseInt(result.get(0).get("total"));
    }

    /**
     * 检查手机号是否已存在
     *
     * @param phone 要检查的手机号
     * @return 存在返回true ，不存在返回false
     */
    public boolean phoneExist(String phone) {
        String sql = "select * from user where phone=?";

        List<Object> para = new ArrayList<>();
        if (StringUtil.isNotEmpty(phone)) {
            para.add(phone);
        }
        List<Map<String, String>> result = JdbcUtils.queryForMap(sql, para);

        return !result.isEmpty();
    }

    /**
     * 骑行用户挂失和解挂
     *
     * @param status 要将骑行用户的status改成这个参数
     * @param uid    the userId
     * @return boolean boolean
     */
    public boolean changeStatus(int status, String uid) {
        String sql = "update user set status=? where userId = ?";
        List<Object> para = new ArrayList<>();
        para.add(status);

        if (StringUtil.isNotEmpty(uid)) {
            para.add(uid);
        }
        return 1 == JdbcUtils.update(sql, para);
    }

    /**
     * 骑行用户重置密码
     *
     * @param password 新密码
     * @param phone    the phone
     * @return boolean boolean
     */
    public boolean changePassword(String password, String phone) {
        String sql = "update user set password=? where phone=?";
        List<Object> para = new ArrayList<>();
        if (StringUtil.isNotEmpty(password)) {
            para.add(password);
        }
        if (StringUtil.isNotEmpty(phone)) {
            para.add(phone);
        }
        return 1 == JdbcUtils.update(sql, para);
    }

    /**
     * 将骑行用户的userInfoId改为参数中的userInfoId
     *
     * @param userId     the user id
     * @param userInfoId the user info id
     * @return boolean boolean
     */
    public boolean changeUserInfoId(String userId, String userInfoId) {
        String sql = "update user set userInfoId=? where userId=?";
        List<Object> para = new ArrayList<>();
        if (StringUtil.isNotEmpty(userInfoId)) {
            para.add(userInfoId);
        }
        if (StringUtil.isNotEmpty(userId)) {
            para.add(userId);
        }
        return 1 == JdbcUtils.update(sql, para);
    }

    /**
     * 判断骑行用户是否已经经过了身份验证
     *
     * @param user 骑行用户
     * @return boolean boolean
     */
    public boolean hasVerified(User user) {
        String sql = "select userInfoId from user where userId=?";

        List<Object> para = new ArrayList<>();
        if (StringUtil.isNotEmpty(user.getUserId())) {
            para.add(user.getUserId());
        }
        List<Map<String, String>> result = JdbcUtils.queryForMap(sql, para);

        return !result.isEmpty();
    }

    /**
     * User prefix search list.
     *
     * @param user the user
     * @return the list
     */
    public List<User> userPrefixSearch(User user) {
        StringBuilder sb = new StringBuilder("select * from user u ");
        List<Object> para = new ArrayList<>();
        if (StringUtil.isNotEmpty(user.getUserId())) {
            sb.append(" and u.userId like ?");
            para.add("%" + user.getUserId() + "%");
        }
        if (StringUtil.isNotEmpty(user.getUserName())) {
            sb.append(" and u.username like ?");
            para.add("%" + user.getUserName() + "%");
        }
        if (StringUtil.isNotEmpty(user.getName())) {
            sb.append(" and u.name like ?");
            para.add("%" + user.getName() + "%");
        }
        if (StringUtil.isNotEmpty(user.getCardNumber())) {
            sb.append(" and u.cardNumber like ?");
            para.add("%" + user.getCardNumber() + "%");
        }
        if (StringUtil.isNotEmpty(user.getPhone())) {
            sb.append(" and u.phone like ?");
            para.add("%" + user.getPhone() + "%");
        }
        sb.append(" and u.status != -2 ");
        String sql = sb.toString().replaceFirst("and", "where");
        List<Object> list = JdbcUtils.queryForArrObject(sql, para, User.class);
        List<User> userList = new ArrayList<>();
        for (Object aList : list) {
            userList.add((User) aList);
        }
        return userList;
    }

    /**
     * Find by username optional.
     *
     * @param username the username
     * @return the optional
     * @throws SQLException the sql exception
     */
    public Optional<User> findByUsername(String username) throws SQLException {
        String sql = "select * from user u where username = ?";
        List<Object> para = new ArrayList<>();
        para.add(username);
        List<Object> list = JdbcUtils.queryForArrObject(sql, para, User.class);
        return list.size() <= 0 ? Optional.empty() : Optional.of((User) list.get(0));
    }

    /**
     * Find by phone optional.
     *
     * @param phone the phone
     * @return the optional
     */
    public Optional<User> findByPhone(String phone) {
        String sql = "select * from user u where phone = ?";
        List<Object> para = new ArrayList<>();
        para.add(phone);
        List<Object> list = JdbcUtils.queryForArrObject(sql, para, User.class);
        return list.size() <= 0 ? Optional.empty() : Optional.of((User) list.get(0));
    }

    /**
     * Recharge int.
     *
     * @param connection the connection
     * @param user       the user
     * @param money      the money
     * @return the int
     * @throws SQLException the sql exception
     */
    public int recharge(Connection connection, User user, BigDecimal money) throws SQLException {
        String sql = "UPDATE user SET balance = balance + ? WHERE userId = ?";
        PreparedStatement ps = connection.prepareStatement(sql);
        ps.setInt(1, money.intValue());
        ps.setString(2, user.getUserId());
        int r = ps.executeUpdate();
        JdbcUtils.closeStatement(ps);
        return r;
    }

    /**
     * 将骑行用户的name改成userInfo表里的name
     *
     * @param userId     the user id
     * @param userInfoId the user info id
     * @author 邓晴予
     */
    public void changeName(String userId, String userInfoId) {
        String sql = "select name from userInfo where userInfoId = ?";
        List<Object> para = new ArrayList<>();
        para.add(userInfoId);
        List<Map<String, String>> list = JdbcUtils.queryForMap(sql, para);
        if (list.isEmpty()) {
            return;
        }
        sql = "update user set name=? where userId=?";
        para = new ArrayList<>();
        if (StringUtil.isNotEmpty(list.get(0).get("name"))) {
            para.add(list.get(0).get("name"));
        }
        if (StringUtil.isNotEmpty(userId)) {
            para.add(userId);
        }
        JdbcUtils.update(sql, para);
    }
}

